﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XW.Data; 
using XW.Utils;
using System.Web;
using Microsoft.AspNetCore.Http;
using System.Data.SqlClient;
using XW.Logging;
using StackExchange.Exceptional;
using Microsoft.AspNetCore.Mvc;
namespace XW.Core
{
    public partial class BaseService : IDisposable
    {
        public ILog  L = LogProvider.GetCurrentClassLogger();
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="module">模块</param>
        /// <param name="tableName">表</param>
        /// <param name="business">业务</param>
        public BaseService(string module, string tableName, string business)
        {
            ModuleName = module;
            _tblName = tableName;
            _business = business;
            _db = GetDb(module); //new DbContext().ConnectionStringName(module, new SqlServerProvider()); 
        }
        public void ChangeDB(string connectstring) {
            _db = GetDbByStr(connectstring, new SqlServerProvider());
        }

        public BaseService(string module, string tableName, string business, IDbProvider provider)
        {
            ModuleName = module;
            _tblName = tableName;
            _business = business;
            _db = GetDb(module, provider); //new DbContext().ConnectionStringName(module, new SqlServerProvider()); 
        }

        public BaseService()
        {
        }

        #region 字段
        /// <summary>
        /// 表
        /// </summary>
        public string _tblName;
        /// <summary>
        /// 业务
        /// </summary>
        private string _business = "";
        private IDbContext _db;
        #endregion

        #region 属性
        /// <summary>
        /// 是否需要添加日志
        /// </summary>
        public virtual bool NeedAddLog { get { return true; } }
        /// <summary>
        /// 模块
        /// </summary>
        public string ModuleName { get; set; }
        public IDbContext db
        {
            get
            {
                return _db;
            }
        }
        #endregion

        public IDbContext GetDb()
        {
            return GetDb(this.ModuleName, new SqlServerProvider());
        }
        public IDbContext GetDb(string connectstringName)
        {
            return GetDb(connectstringName, new SqlServerProvider());
        }
        public IDbContext GetDb(string connectstringName, IDbProvider provider)
        {
            
            return GetDbByStr(ConfigHelper.GetConn(connectstringName), provider);
        }

        public IDbContext GetDbByStr(string connectstring)
        {
            return GetDbByStr(connectstring, new SqlServerProvider());
        }
        public IDbContext GetDbByStr(string connectstring, IDbProvider provider)
        {
            var db = Db.Context(connectstring, provider);

            //db.OnError(e =>
            //{
            //    var ex = e.Exception as SqlException;
            //    L.Error(e.Exception, "错误内容:{0};sql:{1}", e.Exception.Message, e.Command.CommandText);
            //    if (ex != null)
            //    {
            //        var error = "";
            //        switch (ex.Number)
            //        {
            //            case -2:
            //                error = "超时时间已到。在操作完成之前超时时间已过或服务器未响应";
            //                break;
            //            case 4060:
            //                // Invalid Database
            //                error = "数据库不可用,请检查系统设置后重试！";
            //                break;
            //            case 18456:
            //                // Login Failed
            //                error = "登陆数据库失败！";
            //                break;
            //            case 547:
            //                // ForeignKey Violation
            //                error = "数据已经被引用，更新失败，请先删除引用数据并重试！";
            //                break;
            //            case 2627:
            //                // Unique Index/Constriant Violation
            //                error = "主键重复，更新失败！";
            //                break;
            //            case 2601:
            //                // Unique Index/Constriant Violation   
            //                break;
            //            default:
            //                // throw a general DAL Exception   
            //                break;
            //        }
            //        if (!string.IsNullOrEmpty(error))
            //            throw new Exception(error);
            //    }
            //});

            return db;
        }

        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(object obj)
        {
            return Insert(_tblName, obj);
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(string tblName, object obj)
        {
            var columns = GetColumns(obj);
            var sql = _db.Insert(tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            return ExcuteAndRemoveCache(sql);
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int InsertWithUser(object obj)
        {
            return InsertWithUser(_tblName, obj);
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int InsertWithUser(string tblName, object obj)
        {
            var columns = GetColumns(obj);
            var sql = _db.Insert(tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            sql.Column("create_time", DateTime.Now);
            sql.Column("create_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            sql.Column("update_time", DateTime.Now);
            sql.Column("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));

            return ExcuteAndRemoveCache(sql);
        }

        public int InsertWithUserReturnId(object obj)
        {
            var columns = GetColumns(obj);
            var sql = _db.Insert(_tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            sql.Column("create_time", DateTime.Now);
            sql.Column("create_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            sql.Column("update_time", DateTime.Now);
            sql.Column("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));

            return ExcuteReturnLastIdAndRemoveCache<int>(sql);
        }

        public int InsertWithUserReturnId(string tblName, object obj)
        {
            var columns = GetColumns(obj);
            var sql = _db.Insert(tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            sql.Column("create_time", DateTime.Now);
            sql.Column("create_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            sql.Column("update_time", DateTime.Now);
            sql.Column("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));

            return ExcuteReturnLastIdAndRemoveCache<int>(sql);
        }
        /// <summary>
        /// 插入并返回Id
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public TID InsertReturnId<TID>(object obj)
        {

            return InsertReturnId<TID>(_tblName, obj);
        }

        /// <summary>
        /// 插入并返回Id
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public TID InsertReturnId<TID>(string tblName, object obj)
        {
            var columns = GetColumns(obj);
            var sql = _db.Insert(tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            return ExcuteReturnLastIdAndRemoveCache<TID>(sql);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="col"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int Update(object colObj, object whereObj)
        {
            return Update(_tblName, colObj, whereObj);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="col"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int UpdateWithUser(object colObj, object whereObj)
        {
            var update = _db.Update(_tblName);
            var columns = GetColumns(colObj);
            var wheres = GetColumns(whereObj);
            columns.ForEach(kvp => { update.Column(kvp.Key, kvp.Value); });
            wheres.ForEach(kvp => { update.Where(kvp.Key, kvp.Value); });
            update.Column("update_time", DateTime.Now);
            update.Column("update_id", CommonHelper.ToInt64(AuthHelper.GetUserid()));
            return ExcuteAndRemoveCache(update);
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <typeparam name="TOut"></typeparam>
        /// <param name="idField"></param>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <param name="cb"></param>
        public void UpdateDataStatusWithUser<TID>(string idField,TID id, int status , Action<bool,string> cb) {

            var sql = _db.Select<dynamic>(idField+ ", data_status");
            sql.From(_tblName);
            sql.AndWhere(string.Format(" {0} =@{0} ", idField));
            sql.Parameter(idField, id);
            sql.AndWhere(string.Format(" is_delete ={0} ", (int)YesNo.No));
            var list = sql.QueryMany();
            if (!list.Any())
            {
                cb(false,"数据不存在！");
                return;
            }

            if (list.Single().data_status == status)
            {
                cb(false, "状态已修改，请不要重复修改。");
                return;
            }
            object data = null;
            var userId = CommonHelper.ToInt64(AuthHelper.GetUserid());
            if (status == 0)
            {
                data = new { data_status = status, disable_id = 0, disable_time = DBNull.Value };
            }
            else
            {
                data = new { data_status = status, disable_id = userId, disable_time = DateTime.Now };
            }
            IDictionary<string, object> where = new Dictionary<string, object>();
            where.Add(idField, id);
            int result = this.UpdateWithUser(data, where);

            if (result >0 )
            {
                cb(true, "状态修改成功！");
                return;
            }
            else
            {
                cb(false, "状态修改失败！");
                return;
            }
           
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="col"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int Update(string tblName, object colObj, object whereObj)
        {
            var update = _db.Update(tblName);
            var columns = GetColumns(colObj);
            var wheres = GetColumns(whereObj);
            columns.ForEach(kvp => { update.Column(kvp.Key, kvp.Value); });
            wheres.ForEach(kvp => { update.Where(kvp.Key, kvp.Value); });
            return ExcuteAndRemoveCache(update);
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="col"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int Update(object colObj, string where)
        {
            var update = _db.Update(_tblName);
            var columns = GetColumns(colObj);
            columns.ForEach(kvp => { update.Column(kvp.Key, kvp.Value); });
            update.Where(where);
            return ExcuteAndRemoveCache(update);
        }

        public int Update(string sql, object para)
        {
            return Execute(sql, para);
        }

        public int Execute(string sql, object para)
        {
            var columns = GetColumns(para);
            var _sql = _db.Sql(sql);
            columns.ForEach(kvp => { _sql.Parameter(kvp.Key, kvp.Value); });
            return ExcuteAndRemoveCache(_sql);
        }
        public T ExecuteReturnLastId<T>(string sql, object para)
        {
            var columns = GetColumns(para);
            var _sql = _db.Sql(sql);
            columns.ForEach(kvp => { _sql.Parameter(kvp.Key, kvp.Value); });

            return ExcuteReturnLastIdAndRemoveCache<T>(_sql);
        }

        public T ExcuteReturnLastIdAndRemoveCache<T>(IExecuteReturnLastId cmd)
        {
            T result = cmd.ExecuteReturnLastId<T>();
            RemoveCacheByPre();
            return result;
        }

        public int ExcuteAndRemoveCache(IExecute cmd)
        {
            int result = cmd.Execute();
            RemoveCacheByPre();
            return result;
        }
        public int DeleteBySql(string sql, object where)
        {
            IDictionary<string, object> columns = GetColumns(where);
            var _sql = _db.Sql(sql);
            columns.ForEach(kvp => { _sql.Parameter(kvp.Key, kvp.Value); });
            return ExcuteAndRemoveCache(_sql);
        }
        public int Delete(string tblName, object where)
        {
            IDictionary<string, object> columns = GetColumns(where);
            var _sql = _db.Delete(tblName);
            columns.ForEach(kvp => { _sql.Where(kvp.Key, kvp.Value); });
            return ExcuteAndRemoveCache(_sql);
        }
        public int SaveByForm(HttpRequest request,
            string updateColumns,
            string whereColumns,
            string id,
            Func<Dictionary<string, object>, bool> checkIsInsert = null)
        {
            var parameters = GetParamFromForm(request, id, checkIsInsert);
            if (parameters.CheckIsInsert())
            {
                var data = parameters.GetColumns(updateColumns);
                data.create_time = DateTime.Now;
                data.create_id = AuthHelper.GetUserid();
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                return Insert(data);
            }
            else
            {
                var data = parameters.GetColumns(updateColumns);
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                var where = parameters.GetWhere(whereColumns);
                return Update(data, where);
            }
        }

        public int SaveByForm(HttpRequest request,
            string insertColumns,
            string updateColumns,
            string whereColumns,
            string id,
            Func<Dictionary<string, object>, bool> checkIsInsert = null)
        {
            var parameters = GetParamFromForm(request, id, checkIsInsert);
            if (parameters.CheckIsInsert())
            {
                var data = parameters.GetColumns(insertColumns);
                data.create_time = DateTime.Now;
                data.create_id = AuthHelper.GetUserid();
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                return Insert(data);
            }
            else
            {
                var data = parameters.GetColumns(updateColumns);
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                var where = parameters.GetWhere(whereColumns);
                return Update(data, where);
            }
        }
        public int InsertByForm(HttpRequest request, string insertColumns = "")
        {
            var parameters = GetParamFromForm(request);
            var data = parameters.GetColumns(insertColumns);
            data.create_time = DateTime.Now;
            data.create_id = AuthHelper.GetUserid();
            data.update_time = DateTime.Now;
            data.update_id = AuthHelper.GetUserid();
            return Insert(data);
        }
        public int UpdateByForm(HttpRequest request, string updateColumns = "",
            string whereColumns = "")
        {
            var parameters = GetParamFromForm(request);
            var data = parameters.GetColumns(updateColumns);
            data.update_time = DateTime.Now;
            data.update_id = AuthHelper.GetUserid();
            var where = parameters.GetWhere(whereColumns);
            return Update(data, where);
        }
        public int Delete(object where)
        {
            var _sql = _db.Delete(_tblName);
            IDictionary<string, object> columns = GetColumns(where);
            columns.ForEach(kvp => { _sql.Where(kvp.Key, kvp.Value); });
            return ExcuteAndRemoveCache(_sql);
        }
        public int DeleteByWhere(string where)
        {
            var _sql = _db.Delete(_tblName).Where(where);
            return ExcuteAndRemoveCache(_sql);
        }
        public T Single<T>(string column, object where)
        {

            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<T>(column);
            sql.From(_tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }
        public dynamic Single(string column, object where)
        {
            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<dynamic>(column);
            sql.From(_tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }

        public IDbCommand Sql(string sql)
        {
           return _db.Sql(sql); 
        }
       
        public dynamic Single(object where)
        {

            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<dynamic>("*");
            sql.From(_tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }
        public T Get<T>(object where) {
            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<T>("*");
            sql.From(_tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }
        #region Exists
        public bool Exists(object where)
        {
            return Exists(this._tblName, where);
        }
        public bool Exists(string tblName, object where)
        {

            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<int>("count(1)");
            sql.From(tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle() > 0;
        }
        #endregion

        #region Count
        public ISelectBuilder<int> Count() { 
            var sql = _db.Select<int>("count(1)");
            sql.From(_tblName); 
            return sql;
        }

        public int Count(object where)
        {
            return Count(_tblName, where);
        }
        public int Count(string tblName, object where)
        {

            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<int>("count(1)");
            sql.From(tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }
        #endregion

        public List<T> Q<T>(string where = "", string select = "*", string order = "")
        {
            return GetList<T>(_tblName, where, select, order);
        }

        public List<T> GetList<T>(string where = "", string select = "*", string order = "")
        {
            return GetList<T>(_tblName, where, select, order);
        }
        public List<T> GetList<T>(object where = null, string select = "*", string order = "")
        {
            return GetList<T>(_tblName, where, select, order);
        }
     
        public List<T> GetList<T>(string from, string where = "", string select = "*", string order = "")
        {
            var sql = _db.Select<T>(select);
            sql.From(from);
            sql.Where(where);
            if (order.IsNotNullAndEmpty())
            {
                sql.OrderBy(order);
            }
            return sql.QueryMany();
        }

        public List<T> GetListBySql<T>(string sql, object paremeters = null)
        {
            var s = _db.Sql(sql);
            if (paremeters != null)
            {
                IDictionary<string, object> columns = GetColumns(paremeters);
                columns.ForEach(k => {
                    s.Parameter(k.Key, k.Value);
                });
            }
            return s.QueryMany<T>();
        }
        public List<T> GetList<T>(string from, object where = null, string select = "*", string order = "")
        {
            var sql = _db.Select<T>(select);
            sql.From(from);
            if (where != null)
            {
                IDictionary<string, object> columns = GetColumns(where);
                columns.ForEach(k => {
                    sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                    sql.Parameter(k.Key, k.Value);
                });
            }
            if (order.IsNotNullAndEmpty())
            {
                sql.OrderBy(order);
            }
            return sql.QueryMany();
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult<T> Page<T>(string columns, string where, string defaultSort)
        { 
            return Page<T>(columns,_tblName,where,defaultSort);
        }
        public PageResult<dynamic> Page(string columns, string table, string where, string defaultSort) {
            return Page<dynamic>(columns, table, where, defaultSort);
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult<T> Page<T>(string columns, string table, string where, string defaultSort,List<string> extTotalColumns = null)
        {
            var result = new PageResult<T>();
            var page = CommonHelper.GetPageModel(defaultSort);
            result.items = _db.Select<T>(columns).From(table).Where(where).OrderBy(page.SortField + " "+ page.SortType).Paging(page.Page, page.Limit).QueryMany();
            if (where.IsNotNullAndEmpty())
            {
                where = " where " + where;
            }
            if (extTotalColumns.IsNullOrEmpty())
            {
                result.total = _db.Sql(" select count(1) as cnt from " + table + where).QuerySingle<long>();
            }
            else
            {
                List<string> selecteds = new List<string>();
                selecteds.Add(" count(1) as cnt ");
                foreach (var item in extTotalColumns)
                {
                    selecteds.Add(string.Format(" sum({0}) as {0} ", item));
                }
                var  totalDt = _db.Sql(" select  " + string.Join(",", selecteds) + " from " + table + where).QuerySingle<System.Data.DataTable>();
                result.total = 0;
                if (totalDt.Rows.Count >0)
                {
                    System.Data.DataRow dr = totalDt.Rows[0];
                    result.total = CommonHelper.ToInt64(dr["cnt"]);
                    result.ExtTotal = new Dictionary<string, object>();
                    foreach (var item in extTotalColumns)
                    {
                        result.ExtTotal[item] = dr[item];
                    }
                }
            }
           
            return result;
        }
        public PageResult<dynamic> Page(string columns, string table, string where, string sort, int page, int rows) {
            return Page<dynamic>(  columns,   table,   where,   sort,   page,   rows);
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult<T> Page<T>(string columns, string table, string where, string sort, int page, int rows)
        {
            var result = new PageResult<T>();
            result.items = _db.Select<T>(columns).From(table).Where(where).OrderBy(sort).Paging(page, rows).QueryMany();
            if (where.IsNotNullAndEmpty())
            {
                where = " where " + where;
            }
            result.total = _db.Sql(" select count(1) from " + table + where).QuerySingle<long>();
            return result;
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult<T> Page<T>(string columns, string table, string where, string sort, int page, int rows, string connectString)
        {
            return Page<T>(columns, table, (object)where, sort, page, rows, connectString);
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult<T> Page<T>(string columns, string table, Object where, string sort, int page, int rows, string connectString)
        {
            var result = new PageResult<T>();
            var db = GetDbByStr(connectString);
            var selectList = db.Select<T>(columns).From(table);
            var selectTotal = db.Select<long>("count(1)").From(table);
            if (where != null)
            {
                if (where is string && where.ToString().IsNotNullAndEmpty())
                {
                    selectList.AndWhere(where.ToString());
                    selectTotal.AndWhere(where.ToString());
                }
                else
                {
                    IDictionary<string, object> _columns = GetColumns(where);

                    _columns.ForEach(k => {
                        selectList.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                        selectList.Parameter(k.Key, k.Value);
                        selectTotal.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                        selectTotal.Parameter(k.Key, k.Value);
                    });
                }
            } 
            result.items = selectList.OrderBy(sort).Paging(page, rows).QueryMany();
            result.total = selectTotal.QuerySingle();
            db.Dispose();
            return result;
        }
        public void Dispose()
        {
            if (_db != null)
            {
                _db.Dispose();
                _db = null;
            }
            if (true)
            {

            }
        }


        /// <summary>
        /// Trans the excute.
        /// </summary>
        /// <returns>System.Int32.</returns>
        public void TranExcute()
        {
            var db = _db.UseTransaction(true);
        }

        /// <summary>
        /// 获取单号
        /// </summary>
        /// <param name="column"></param>
        /// <param name="codepre"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public string GetCode(string column, string codepre, int length, string tblName = "")
        {
            if (tblName.IsNullOrEmpty()) tblName = _tblName;

            string maxCode = this.db.Sql("select max(" + column + ") as maxcode from " + tblName + " where " + column + " like '" + codepre + "%' and LEN(" + column + " ) = " + (length + codepre.Length) + " ").QuerySingle<string>();
            long maxValue = 1;
            if (maxCode.IsNotNullAndEmpty())
            {
                maxCode = maxCode.Substring(codepre.Length);
                maxValue = CommonHelper.ToInt64(maxCode) + 1;
            }

            return codepre + (maxValue.ToString().PadLeft(length, '0'));
        }

        /// <summary>
        /// 获取返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetData(string sql)
        {
            string result = "";
            if (sql.IsNotNullAndEmpty())
            {
                 result = this.db.Sql(sql).QuerySingle<string>();
            }
            return result;
        }
        /// <summary>
        /// 检查是否重复
        /// 不检查已经删除的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="codeField">The code field.</param>
        /// <param name="codeVal">The code value.</param>
        /// <param name="idField">The identifier field.</param>
        /// <param name="idVal">The identifier value.</param>
        /// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
        public bool CheckDuplication<T>(string codeField ,string codeVal,string idField,T idVal) {
            var _sql = _db.Sql(" select count(1) as cnt from "+ _tblName 
                + " where " + codeField + " = @code and  " + idField + " <> @id  and is_delete <> 1 ");
            
            return  _sql.Parameter("code", codeVal).Parameter("id",idVal).QuerySingle<int>() > 0;
        }

        /// <summary>
        /// 事务执行
        /// </summary>
        /// <param name="action">事务的动作</param>
        /// <param name="success">成功时执行</param>
        /// <param name="error">失败时执行</param>
        /// <returns>
        /// true  事务执行成功
        /// false 事务执行失败
        /// </returns>
        public bool Excute4Tran(Action<IDbContext> action, Action success = null, Action<Exception> error = null)
        {

            using (var db = GetDb())
            using (var tran = db.UseTransaction(true))
            {

                try
                {
                    action(tran);
                    tran.Commit();

                    success?.Invoke();
                    return true;
                }
                catch (Exception e)
                {
                    e?.Log( SXHttpContext.Current);
                    L.Error(e, "错误内容：{0}", e.Message);
                    tran.Rollback();
                    error?.Invoke(e);
                    return false;
                }
            }
        }
        /// <summary>
        /// 事务执行
        /// </summary>
        /// <param name="sqlList">sql 列表</param>
        /// <param name="success">成功时执行</param>
        /// <param name="error">失败时执行</param>
        /// <returns>
        /// true  事务执行成功
        /// false 事务执行失败
        /// </returns>
        public bool Excute4Tran(List<string> sqlList, Action success = null, Action<Exception> error = null)
        {
            return Excute4Tran((tran) => {

                foreach (var sql in sqlList)
                {
                    tran.Sql(sql).Execute();
                }
            }, success, error);
        }

        public bool Excute4Tran(List<SqlCmd> sqlList, Action success = null, Action<Exception> error = null)
        {
            return Excute4Tran((tran) => {

                foreach (var sql in sqlList)
                {
                   var r = sql.Excute(tran);
                   // this.L.Debug("sql:{0}, 结果：{1}",sql,r);
                }
            }, success, error);
        } 
        public virtual JsonResult Json(object? data)
        {
            return new JsonResult(data);
        }
        /// <summary>
        /// 获取失败结果
        /// </summary>
        /// <param name="code"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public JsonResult GetErrorResult()
        {
            return Json(new { resultCode = ResultCode.Error, message = "操作失败！" });
        }
        /// <summary>
        /// 获取失败结果
        /// </summary>
        /// <param name="code"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public JsonResult GetErrorResult(string message)
        {
            return Json(new { resultCode = ResultCode.Error, message = message });
        }
        /// <summary>
        /// 获取失败结果
        /// </summary>
        /// <param name="code"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public JsonResult GetErrorResult(string message, object parameters)
        {
            return Json(new { resultCode = ResultCode.Error, message = message, parameters = parameters });
        }
        /// <summary>
        /// 获取成功结果
        /// </summary>
        /// <param name="code"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public JsonResult GetSuccessResult(string message)
        {
            return Json(new { resultCode = ResultCode.Success, message = message });
        }

        public JsonResult GetSuccessResult(object data)
        {
            return Json(new { resultCode = ResultCode.Success, data = data });
        }
    }
}
